package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.test.StudentTest;
import cn.ztydata.teachingsystem.heaven.entity.test.Test;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 作业测试Dao类
 * Created by chenxin on 2015/3/18.
 */
@Repository
public class TestDao extends BaseDao {
    /**
     * 查询学生作业测试列表
     *
     * @param groupId 群组ID
     * @param type 作业类型
     * @param method 作业方式
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param search 搜索关键字
     * @param studentId 学生ID
     * @return Page 分页数据
     *
     * @author cx
     * @since 2015/03/19
     */
    public Page<Test> getListForStudent(final long groupId, final int type, final int method,
                                        final int pageNum, final int pageSize, final String search, final Long studentId) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        StringBuilder sql = new StringBuilder("SELECT t.id,t.sName,t.tiMethod,t.dtStartTime,t.dtEndTime,t.siScore,t.iFirstLevel,t.iSecondLevel,")
                .append("t.siStudentCnt,t.iGroupId,cg.sName AS groupName,st.siScore AS grade,st.tiStatus")
                .append(" FROM t_test t INNER JOIN t_course_group cg ON t.iGroupId=cg.id")
                .append(" LEFT JOIN t_student_test st ON t.id=st.iTestId AND st.iStudentId=:studentId");
        sps.addValue("studentId", studentId);
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_test t");


        //定义where条件
        StringBuilder where = new StringBuilder(" WHERE t.tiType=:type AND t.iGroupId=:groupId");
        sps.addValue("type", type).addValue("groupId", groupId);

        //方式：线下/线上
        if (method < 2) {
            log.debug("作业方式筛选:method < 2");

            where.append(" AND t.tiMethod=:method");
            sps.addValue("method", method);
        }

        //搜索关键字
        if (!StringUtils.isEmpty(search)) {
            log.debug("搜索关键字:!StringUtils.isEmpty(search)");

            where.append(" AND UPPER(t.sName) LIKE :search");
            sps.addValue("search", "%" + search.toUpperCase() + "%");
        }

        //获取总数
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total < 1) {
            log.debug("作业列表总数为0:total < 1");

            return new Page<>();
        }

        //追加排序和limit
        sql.append(where).append(" ORDER BY t.id DESC").append(getLimitStr(pageNum, pageSize));

        List<Test> tests = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Test>() {
            @Override
            public Test mapRow(ResultSet rs, int rowNum) throws SQLException {
                Test test = new Test();

                test.setId(rs.getLong("id"));
                test.setName(rs.getString("sName"));
                test.setMethod(rs.getInt("tiMethod"));
                test.setStartTime(rs.getDate("dtStartTime"));
                test.setEndTime(rs.getDate("dtEndTime"));
                test.setScore(rs.getInt("siScore"));
                test.setFirstLevelId(rs.getLong("iFirstLevel"));
                test.setSecondLevelId(rs.getLong("iSecondLevel"));
                test.setTotalNum(rs.getInt("siStudentCnt"));
                test.setGroupId(rs.getLong("iGroupId"));
                test.setGroupName(rs.getString("groupName"));
                test.setStatus(rs.getInt("tiStatus"));

                StudentTest studentTest = new StudentTest();
                studentTest.setStatus(rs.getInt("tiStatus"));
                studentTest.setScore(rs.getInt("grade"));

                test.setStudentTest(studentTest);

                return test;
            }
        });

        log.debug("return 分页学生作业列表");
        return new Page<>(total, pageSize, pageNum, tests);
    }

    /**
     * 查询群组作业及学生得分
     *
     * @param groupId 群组ID
     * @param type 作业类型
     * @param method 作业方式
     * @param studentId 学生ID
     * @return List
     *
     * @author cx
     * @since 2015/03/20
     */
    public List<Test> getGroupTestsWithGrade(final long groupId, final int type, final int method, final long studentId, final String keyword) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        StringBuilder sql = new StringBuilder("SELECT t.id,t.sName,t.tiMethod,t.dtEndTime,st.siScore")
                .append(" FROM t_test t LEFT JOIN t_student_test st")
                .append(" ON t.id=st.iTestId AND st.iStudentId=:studentId");
        sps.addValue("studentId", studentId);

        //定义where条件
        StringBuilder where = new StringBuilder(" WHERE t.iGroupId=:groupId AND t.tiType=:type");
        sps.addValue("groupId", groupId).addValue("type", type);

        //作业方式
        if (method < 2) {
            log.debug("作业方式判定:method < 2");

            where.append(" AND t.tiMethod=:method");
            sps.addValue("method", method);
        }

        //搜索关键字
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("搜索关键字非空:!StringUtils.isEmpty(keyword)");

            where.append(" AND UPPER(t.sName) LIKE :keyword");
            sps.addValue("keyword", "%" + keyword.toUpperCase() + "%");
        }

        //结束时间正序
        sql.append(where).append(" ORDER BY dtEndTime");

        log.debug("查询群组作业及学生得分");
        return namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Test>() {
            @Override
            public Test mapRow(ResultSet rs, int rowNum) throws SQLException {
                Test test = new Test();

                test.setId(rs.getLong("id"));
                test.setName(rs.getString("sName"));
                test.setEndTime(rs.getDate("dtEndTime"));
                test.setMethod(rs.getInt("tiMethod"));

                StudentTest studentTest = new StudentTest();

                studentTest.setScore(rs.getInt("siScore"));

                test.setStudentTest(studentTest);

                return test;
            }
        });
    }

    /**
     * 查询学生最近的作业、测试列表
     *
     * @param groupIds 群组ID集合
     * @param type 作业类型
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param studentId 学生ID
     * @return Page
     *
     * @author cx
     * @since 2015/03/20
     */
    public Page<Test> getLastTestsForStudent(final List<Long> groupIds, final int type, final int pageNum, final int pageSize, final long studentId) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        StringBuilder sql = new StringBuilder("SELECT t.id,t.sName,t.iGroupId,t.tiMethod,t.dtStartTime,t.dtEndTime,st.tiStatus,st.siScore")
                .append(" FROM t_test t LEFT JOIN t_student_test st")
                .append(" ON t.id=st.iTestId AND st.iStudentId=:studentId");
        sps.addValue("studentId", studentId);
        StringBuilder countSql = new StringBuilder("SELECT COUNT(id) FROM t_test t");

        //定义where条件
        StringBuilder where = new StringBuilder(" WHERE t.tiType=:type AND t.iGroupId IN (:groupIds)");
        sps.addValue("type", type).addValue("groupIds", groupIds);

        //获取总数
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total < 1) {
            log.debug("作业列表总数:total < 1");
            return new Page<>();
        }

        //追加排序和limit
        sql.append(where).append(" ORDER BY t.dtEndTime DESC").append(getLimitStr(pageNum, pageSize));

        List<Test> tests = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Test>() {
            @Override
            public Test mapRow(ResultSet rs, int rowNum) throws SQLException {
                Test test = new Test();

                test.setId(rs.getLong("id"));
                test.setName(rs.getString("sName"));
                test.setGroupId(rs.getLong("iGroupId"));
                test.setMethod(rs.getInt("tiMethod"));
                test.setStartTime(rs.getDate("dtStartTime"));
                test.setEndTime(rs.getDate("dtEndTime"));

                StudentTest studentTest = new StudentTest();
                studentTest.setStatus(rs.getInt("tiStatus"));
                studentTest.setScore(rs.getInt("siScore"));

                test.setStudentTest(studentTest);

                return test;
            }
        });

        log.debug("查询学生最近的作业测试列表");
        return new Page<>(total, pageSize, pageNum, tests);
    }

   /**
     * 教师作业列表
     * @param keyword 搜索关键字
     * @param type 类型（0：作业，1：测试）
     * @param groupId 群组编号
     * @param method 方式（0：线上，1：线下）
     * @param currentPage 当前页
     * @param pageSize 每页显示条数
     * @return List
     *
     * @author fanruiping
     * @since 2015-03-20
     */
    public Page<Test> getListForTeacher(Long courseId,Long chapterId,Long userId,String keyword,int type,Long groupId,int method,int currentPage,int pageSize){
        //拼合where语句
        String where = " WHERE t.tiType=:type";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("userId",userId).addValue("type", type);

        //判断是否限制课程编号
        if(0 != courseId){
            log.debug("限制课程编号:0 != courseId");

            where += " AND iCourseId=:courseId";

            params.addValue("courseId", courseId);
        }

        //判断是否限制章节编号
        if(0 != chapterId){
            log.debug("限制章节编号:0 != chapterId");

            where += " AND (iFirstLevel=:chapterId OR iSecondLevel=:chapterId)";

            params.addValue("chapterId", chapterId);
        }

        //判断是否限制创建人
        if(0 != userId){
            log.debug("限制创建人:0 != userId");

            where += " AND t.iCreatorId=:userId";

            params.addValue("userId", userId);
        }else {
            log.debug("不限制创建人:0 == userId");

            where += " AND t.tiOpen=1";
        }

        //搜索关键字是否为空
        if(!keyword.isEmpty()){
            log.debug("限制作业名称匹配关键字:!keyword.isEmpty()");

            where += " AND UPPER(t.sName) LIKE :keyword";

            params.addValue("keyword", "%"+StringUtils.upperCase(keyword)+"%");
        }

        //是否限制课程群组
        if(0 != groupId){
            log.debug("限制所属课程群组:0 != groupId");

            where += " AND t.iGroupId=:groupId";

            params.addValue("groupId", groupId);
        }

        //是否限制方式
        if(2 != method){
            log.debug("限制作业答题方式:2 != method");

            where += " AND t.tiMethod=:method";

            params.addValue("method", method);
        }

        //总数sql
        String totalSql = "SELECT COUNT(t.id) FROM t_test t"+where;

        int total = namedParameterJdbcTemplate.queryForObject(totalSql,params,Integer.class);

        //判断数据条数
        if(0 == total){
            log.debug("获取到的作业列表数据条数为0");

            return new Page<>();
        }

        //数据sql
        String dataSql = "SELECT t.id,t.iGroupId,c.sName groupName,t.sName testName,t.tiMethod,t.dtStartTime,t.dtEndTime,t.iFirstLevel,t.iSecondLevel," +
                "t.siScore,t.siStudentCnt,t.tiReadOver FROM t_test t LEFT JOIN t_course_group c ON t.iGroupId=c.id"+where+" ORDER BY t.dtCreate DESC"+getLimitStr(currentPage, pageSize);

        List<Test> testList = namedParameterJdbcTemplate.query(dataSql, params, new RowMapper<Test>() {

            @Override
            public Test mapRow(ResultSet rs, int i) throws SQLException {
                Test test = new Test();

                test.setId(rs.getLong("id"));
                test.setGroupId(rs.getLong("iGroupId"));
                test.setGroupName(rs.getString("groupName"));
                test.setName(rs.getString("testName"));
                test.setMethod(rs.getInt("tiMethod"));
                test.setStartTime(rs.getDate("dtStartTime"));
                test.setEndTime(rs.getDate("dtEndTime"));
                test.setFirstLevelId(rs.getLong("iFirstLevel"));
                test.setSecondLevelId(rs.getLong("iSecondLevel"));
                test.setScore(rs.getInt("siScore"));
                test.setTotalNum(rs.getInt("siStudentCnt"));
                test.setReadOver(rs.getInt("tiReadOver"));

                return test;
            }
        });

        return new Page<>(total,pageSize,currentPage,testList);
    }

    /**
     * 查询指定作业是否属于指定学生
     * 
     * @param testId 作业ID
     * @param studentNum 学生学号
     * @return boolean
     * 
     * @author cx
     * @since 2015/3/21
     */
    public boolean hasTestForStudent(final long testId, final String studentNum) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(t.id)")
                .append(" FROM t_test t JOIN t_course_group cg ON t.iGroupId=cg.id")
                .append(" JOIN t_student_course sc ON cg.sCode=sc.sCourseCode")
                .append(" WHERE t.id=:testId AND sc.sStudentNum=:studentNum");

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("testId", testId)
                .addValue("studentNum", studentNum);

        log.debug("查询指定作业是否属于指定学生");
        //执行查询
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), sps, Integer.class) > 0;
    }

    /**
     * 判断作业是否属于指定教师
     * @param teacherId 教师编号
     * @param testId 作业编号
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-04-14
     */
    public boolean checkTeacherTest(Long teacherId, Long testId){
        try{
            String sql = "SELECT id FROM t_test WHERE id=:id AND iCreatorId=:teacherId";

            MapSqlParameterSource param = new MapSqlParameterSource();
            param.addValue("id", testId);
            param.addValue("teacherId", teacherId);

            return namedParameterJdbcTemplate.queryForObject(sql, param, Integer.class) > 0;
        }catch (EmptyResultDataAccessException ex){
            return false;
        }
    }

    /**
     * 查询指定习题是否属于指定作业
     *
     * @param testId 作业ID
     * @param exerciseId 习题ID
     * @return boolean
     *
     * @author cx
     * @since 2015/3/30
     */
    public boolean hasExerciseForTest(final long testId, final long exerciseId) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_test_exercise WHERE iTestId=:testId AND iExerciseId=:exerciseId");

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("testId", testId)
                .addValue("exerciseId", exerciseId);

        log.debug("查询指定习题是否属于指定作业");
        //执行查询
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), sps, Integer.class) > 0;
    }

    /**
     * 新增线下作业/测试
     * @param test 作业/测试实体
     *
     * @author fanruiping
     * @since 2015-03-20
     */
    public boolean addOffline(Test test){
        KeyHolder keyHolder = new GeneratedKeyHolder();

        String sql = "INSERT INTO t_test(sName,tiType,tiMethod,dtStartTime,dtEndTime,siScore,iSpecId,iCourseId," +
                "iGroupId,iFirstLevel,iSecondLevel,tiOpen,iCreatorId,dtCreate,sTestFileName,sTestDownPath," +
                "sAnswerFileName,sAnswerDownPath,sInfo,iResourceId,siStudentCnt,tiReferenceNew) VALUES(:name,:type,1,:startTime," +
                ":endTime,:score,:specId,:courseId,:groupId,:firstLevel,:secondLevel,:open,:creatorId,:createTime," +
                ":testFileName,:testDownPath,:answerFileName,:answerDownPath,:info,:resourceId,:studentCnt,:referenceNew)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("name",test.getName()).addValue("type",test.getType())
                .addValue("startTime",test.getStartTime()).addValue("endTime",test.getEndTime()).addValue("score", test.getScore())
                .addValue("specId", test.getSpecId()).addValue("courseId", test.getCourseId()).addValue("groupId",test.getGroupId())
                .addValue("firstLevel", test.getFirstLevelId()).addValue("secondLevel", test.getSecondLevelId()).addValue("open",test.getIsOpen())
                .addValue("creatorId", test.getCreatorId()).addValue("createTime", test.getCreateTime()).addValue("testFileName",test.getTestFileName())
                .addValue("testDownPath", test.getTestDownloadPath()).addValue("answerFileName", test.getAnswerFileName())
                .addValue("answerDownPath", test.getAnswerDownloadPath()).addValue("info",test.getRemark()).addValue("resourceId", test.getResourceId())
                .addValue("studentCnt", test.getTotalNum()).addValue("referenceNew",test.getIsRefNew());

        int result = namedParameterJdbcTemplate.update(sql, params, keyHolder);

        //判断作业数据是否新增成功
        if(result == 1){
            log.debug("线下作业添加成功:result == 1");

            test.setId(keyHolder.getKey().longValue());

            return true;
        }

        return false;
    }

    /**
     * 新增线上作业/测试
     * @param test 作业/测试实体
     *
     * @author fanruiping
     * @since 2015-03-20
     */
    public boolean addOnline(Test test){
        KeyHolder keyHolder = new GeneratedKeyHolder();

        String sql = "INSERT INTO t_test(sName,tiType,tiMethod,dtStartTime,dtEndTime,siScore,iSpecId,iCourseId," +
                "iGroupId,iFirstLevel,iSecondLevel,tiCheckTip,tiOpen,iCreatorId,dtCreate," +
                "sInfo,siStudentCnt,tiReferenceNew) VALUES(:name,:type,0,:startTime," +
                ":endTime,:score,:specId,:courseId,:groupId,:firstLevel,:secondLevel,:checkTip,:open,:creatorId,:createTime," +
                ":info,:studentCnt,:referenceNew)";

        MapSqlParameterSource params = new MapSqlParameterSource().addValue("name", test.getName()).addValue("type", test.getType())
                .addValue("startTime", test.getStartTime()).addValue("endTime", test.getEndTime()).addValue("score", test.getScore())
                .addValue("specId", test.getSpecId()).addValue("courseId", test.getCourseId()).addValue("groupId", test.getGroupId())
                .addValue("firstLevel", test.getFirstLevelId()).addValue("secondLevel", test.getSecondLevelId()).addValue("checkTip", test.getShowTips())
                .addValue("open", test.getIsOpen()).addValue("creatorId", test.getCreatorId()).addValue("createTime", test.getCreateTime())
                .addValue("info", test.getRemark()).addValue("studentCnt", test.getTotalNum()).addValue("referenceNew",test.getIsRefNew());

        int result = namedParameterJdbcTemplate.update(sql, params, keyHolder);

        //判断作业数据是否新增成功
        if(result == 1){
            log.debug("线上作业新增成功:result == 1");

            test.setId(keyHolder.getKey().longValue());

            return true;
        }

        return false;
    }

    /**
     * 获取作业或测试信息（简单的）
     * @param id 作业或测试编号
     * @return Test
     *
     * @author fanruiping
     * @since 2015-03-21
     */
    public Test getSimpleInfo(Long id){
        try{
            String sql = "SELECT siScore,sTestFileName,sTestDownPath,sTestShowPath,sAnswerFileName,sAnswerDownPath," +
                    "sAnswerShowPath,sInfo,iResourceId FROM t_test WHERE id=:id";

            return namedParameterJdbcTemplate.queryForObject(sql,new MapSqlParameterSource().addValue("id",id),new RowMapper<Test>() {
                @Override
                public Test mapRow(ResultSet rs, int i) throws SQLException {
                    Test test = new Test();

                    test.setScore(rs.getInt("siScore"));
                    test.setTestFileName(rs.getString("sTestFileName"));
                    test.setTestDownloadPath(rs.getString("sTestDownPath"));
                    test.setTestShowPath(rs.getString("sTestShowPath"));
                    test.setAnswerFileName(rs.getString("sAnswerFileName"));
                    test.setAnswerDownloadPath(rs.getString("sAnswerDownPath"));
                    test.setAnswerShowPath(rs.getString("sAnswerShowPath"));
                    test.setRemark(rs.getString("sInfo"));
                    test.setResourceId(rs.getLong("iResourceId"));

                    return test;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            return null;
        }
    }

    /**
     * 获取作业或测试详细信息
     * @param id 作业或测试编号
     * @return Test
     *
     * @author fanruiping
     * @since 2015-03-25
     */
    public Test getDetail(Long id){
        try{
            String sql = "SELECT * FROM t_test WHERE id=:id";

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), new RowMapper<Test>() {
                @Override
                public Test mapRow(ResultSet rs, int i) throws SQLException {
                    Test test = new Test();

                    test.setId(rs.getLong("id"));
                    test.setName(rs.getString("sName"));
                    test.setType(rs.getInt("tiType"));
                    test.setMethod(rs.getInt("tiMethod"));
                    test.setStartTime(rs.getDate("dtStartTime"));
                    test.setEndTime(rs.getDate("dtEndTime"));
                    test.setScore(rs.getInt("siScore"));
                    test.setSpecId(rs.getLong("iSpecId"));
                    test.setGroupId(rs.getLong("iGroupId"));
                    test.setCourseId(rs.getLong("iCourseId"));
                    test.setFirstLevelId(rs.getLong("iFirstLevel"));
                    test.setSecondLevelId(rs.getLong("iSecondLevel"));
                    test.setShowTips(rs.getInt("tiCheckTip"));
                    test.setIsOpen(rs.getInt("tiOpen"));
                    test.setTestFileName(rs.getString("sTestFileName"));
                    test.setTestDownloadPath(rs.getString("sTestDownPath"));
                    test.setTestShowPath(rs.getString("sTestShowPath"));
                    test.setAnswerFileName(rs.getString("sAnswerFileName"));
                    test.setAnswerDownloadPath(rs.getString("sAnswerDownPath"));
                    test.setAnswerShowPath(rs.getString("sAnswerShowPath"));
                    test.setRemark(rs.getString("sInfo"));
                    test.setResourceId(rs.getLong("iResourceId"));
                    test.setTotalNum(rs.getInt("siStudentCnt"));
                    test.setReadOver(rs.getInt("tiReadOver"));
                    test.setCreatorId(rs.getLong("iCreatorId"));

                    return test;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定作业明细:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 删除学生线下作业（未提交）
     *
     * @param studentId 学生ID
     * @param testId 作业ID
     * @return int
     *
     * @author cx
     * @since 2015/3/21
     */
    public int deleteTestFile(final long studentId, final long testId) {
        String sql = "DELETE FROM t_student_test WHERE iStudentId=:studentId AND iTestId=:testId AND tiStatus=0";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("studentId", studentId)
                .addValue("testId", testId);

        log.debug("删除学生线下作业");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 查询学生保存的线下作业文件
     *
     * @param testId 作业ID
     * @param studentId 学生ID
     * @return StudentTest
     *
     * @author cx
     * @since 2015/3/20
     */
    public StudentTest getSavedTest(final long testId, final long studentId) {
        String sql = "SELECT sSubmittedFileName,sSubmittedDownPath,sSubmittedShowPath FROM t_student_test " +
                "WHERE iTestId=:testId AND iStudentId=:studentId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("testId", testId).addValue("studentId", studentId);

        try {
            log.debug("查询学生保存的线下作业文件");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<StudentTest>() {
                @Override
                public StudentTest mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StudentTest studentTest = new StudentTest();

                    studentTest.setSubmittedFileName(rs.getString("sSubmittedFileName"));
                    studentTest.setSubmittedDownPath(rs.getString("sSubmittedDownPath"));
                    studentTest.setSubmittedShowPath(rs.getString("sSubmittedShowPath"));

                    return studentTest;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("学生线下作业文件为null");
            return null;
        }
    }

    /**
     * 更新作业/测试数据
     * @param test 作业/测试实体
     * @return int
     *
     * @author fanruiping
     * @since 2015-03-27
     */
    public int update(Test test){
        String sql = "UPDATE t_test SET sName=:name,dtStartTime=:startTime,dtEndTime=:endTime,siScore=:score,tiCheckTip=:tip," +
                "tiOpen=:open,sTestFileName=:testFileName,sTestDownPath=:testDownPath,sTestShowPath=:testShowPath,sAnswerFileName=:answerFileName," +
                "sAnswerDownPath=:answerDownPath,sAnswerShowPath=:answerShowPath,sInfo=:info,iResourceId=:resourceId " +
                "WHERE id=:id";

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("name",test.getName()).addValue("startTime",
                test.getStartTime()).addValue("endTime",test.getEndTime()).addValue("score",test.getScore()).addValue("tip",test.getShowTips())
                .addValue("open",test.getIsOpen()).addValue("testFileName",test.getTestFileName()).addValue("testDownPath",test.getTestDownloadPath())
                .addValue("testShowPath",test.getTestShowPath()).addValue("answerFileName",test.getAnswerFileName())
                .addValue("answerDownPath",test.getAnswerDownloadPath()).addValue("answerShowPath",test.getAnswerShowPath())
                .addValue("info",test.getRemark()).addValue("resourceId",test.getResourceId()).addValue("id",test.getId());

        return namedParameterJdbcTemplate.update(sql,param);
    }

    /**
     * 删除作业或测试
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-03-27
     */
    public int delete(Long testId){
        String sql = "DELETE FROM t_test WHERE id=:id";

        return namedParameterJdbcTemplate.update(sql,new MapSqlParameterSource().addValue("id",testId));
    }

    /**
     * 资源是否被引用
     * @param resourceId 资源编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-03-31
     */
    public int isQuoteByRscId(Long resourceId){
        try{
            String sql = "SELECT id FROM t_test WHERE iResourceId=:resourceId AND tiMethod=1 LIMIT 1";

            return namedParameterJdbcTemplate.queryForObject(sql,new MapSqlParameterSource().addValue("resourceId",resourceId),Integer.class);
        }catch (EmptyResultDataAccessException ex){
            return 0;
        }
    }

    /**
     * 查询学生作业提交信息
     * 
     * @param testId 作业ID
     * @param studentId 学生ID
     * @return StudentTest
     * 
     * @author cx
     * @since 2015/3/31
     */
    public StudentTest getStudentTest(final long testId, final long studentId) {
        String sql = "SELECT id,siScore,tiStatus,iLastPosition,siSubjective,tiReadOver FROM t_student_test WHERE iTestId=:testId AND iStudentId=:studentId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("testId", testId).addValue("studentId", studentId);

        try {
            log.debug("查询学生作业提交信息");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<StudentTest>() {
                @Override
                public StudentTest mapRow(ResultSet resultSet, int i) throws SQLException {
                    StudentTest studentTest = new StudentTest();

                    studentTest.setId(resultSet.getLong("id"));
                    studentTest.setTestId(testId);
                    studentTest.setStudentId(studentId);
                    studentTest.setScore(resultSet.getInt("siScore"));
                    studentTest.setStatus(resultSet.getInt("tiStatus"));
                    studentTest.setLastPosition(resultSet.getLong("iLastPosition"));
                    studentTest.setSubjectiveCnt(resultSet.getInt("siSubjective"));
                    studentTest.setReadOver(resultSet.getInt("tiReadOver"));

                    return studentTest;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("作业提交信息为null");
            return null;
        }
    }

    /**
     * 更新学生作业信息
     * 
     * @param studentTest 学生作业信息
     * @return int
     * 
     * @author cx
     * @since 2015/3/31
     */
    public int updateStudentTest(final StudentTest studentTest) {
        String sql = "UPDATE t_student_test SET siScore=:score,tiStatus=:status,iLastPosition=:lastPosition,siSubjective=:subjectiveCnt," +
                "tiReadOver=:readOver WHERE iStudentId=:studentId AND iTestId=:testId";
        
        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("score", studentTest.getScore())
                .addValue("status", studentTest.getStatus())
                .addValue("lastPosition", studentTest.getLastPosition())
                .addValue("subjectiveCnt", studentTest.getSubjectiveCnt())
                .addValue("readOver", studentTest.getReadOver())
                .addValue("studentId", studentTest.getStudentId())
                .addValue("testId", studentTest.getTestId());

        log.debug("更新学生作业信息");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 保存学生作业信息
     * 
     * @param studentTest 学生作业信息
     * @return int
     * 
     * @author cx
     * @since 2015/3/31
     */
    public int saveStudentTest(final StudentTest studentTest) {
        String sql = "INSERT INTO t_student_test(iStudentId,iTestId,siScore,tiStatus,iLastPosition,siSubjective,tiReadOver) " +
                "VALUES(:studentId,:testId,:score,:status,:lastPosition,:subjectiveCnt,:readOver)";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("score", studentTest.getScore())
                .addValue("status", studentTest.getStatus())
                .addValue("studentId", studentTest.getStudentId())
                .addValue("testId", studentTest.getTestId())
                .addValue("lastPosition", studentTest.getLastPosition())
                .addValue("subjectiveCnt", studentTest.getSubjectiveCnt())
                .addValue("readOver", studentTest.getReadOver());

        log.debug("保存学生作业信息");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 设置作业为已批阅
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public int setHaveRead(Long testId){
        String sql = "UPDATE t_test SET tiReadOver=1 WHERE id=:testId";

        return namedParameterJdbcTemplate.update(sql,new MapSqlParameterSource("testId",testId));
	}

    /**
     * 设置作业为已批阅
     * @param testId 作业编号
     * @return int
     *
     * @author fanruiping
     * @since 2015-04-06
     */
    public int setUnHaveRead(Long testId){
        String sql = "UPDATE t_test SET tiReadOver=1 WHERE id=:testId";

        return namedParameterJdbcTemplate.update(sql,new MapSqlParameterSource("testId",testId));
    }

    /**
     * 查询作业的构建信息
     * 
     * @param testId 作业ID
     * @return Test
     * 
     * @author cx
     * @since 2015/4/3
     */
    public Test getBuildInfo(final long testId) {
        String sql = "SELECT id,sName,tiType,tiMethod,dtStartTime,dtEndTime,siScore,tiCheckTip,tiOpen,tiReferenceNew," +
                "iCreatorId,dtCreate FROM t_test WHERE id=:testId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("testId", testId);

        try {
            log.debug("查询作业的构建信息");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<Test>() {
                @Override
                public Test mapRow(ResultSet resultSet, int i) throws SQLException {
                    Test test = new Test();

                    test.setId(testId);
                    test.setName(resultSet.getString("sName"));
                    test.setType(resultSet.getInt("tiType"));
                    test.setMethod(resultSet.getInt("tiMethod"));
                    test.setStartTime(resultSet.getDate("dtStartTime"));
                    test.setEndTime(resultSet.getDate("dtEndTime"));
                    test.setScore(resultSet.getInt("siScore"));
                    test.setShowTips(resultSet.getInt("tiCheckTip"));
                    test.setIsOpen(resultSet.getInt("tiOpen"));
                    test.setIsRefNew(resultSet.getInt("tiReferenceNew"));
                    test.setCreatorId(resultSet.getLong("iCreatorId"));
                    test.setCreateTime(resultSet.getTimestamp("dtCreate"));

                    return test;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("作业构建信息为null");
            return null;
        }
    }

    /**
     * 查询学生作业的文件信息（用于线下作业）
     * 
     * @param studentId 学生ID
     * @param testId 作业ID
     * @return StudentTest
     * 
     * @author cx
     * @since 2015/4/18
     */
    public StudentTest getSTFileInfo(final long studentId, final long testId) {
        String sql = "SELECT id,sSubmittedFileName,sSubmittedDownPath,sSubmittedShowPath,sCorrectedFileName,sCorrectedDownPath," +
                "sCorrectedShowPath FROM t_student_test WHERE iStudentId=:studentId AND iTestId=:testId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("studentId", studentId).addValue("testId", testId);

        try {
            log.debug("查询学生作业文件信息");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<StudentTest>() {
                @Override
                public StudentTest mapRow(ResultSet rs, int rowNum) throws SQLException {
                    StudentTest studentTest = new StudentTest();

                    studentTest.setId(rs.getLong("id"));
                    studentTest.setStudentId(studentId);
                    studentTest.setTestId(testId);
                    studentTest.setSubmittedFileName(rs.getString("sSubmittedFileName"));
                    studentTest.setSubmittedDownPath(rs.getString("sSubmittedDownPath"));
                    studentTest.setSubmittedShowPath(rs.getString("sSubmittedShowPath"));
                    studentTest.setCorrectedFileName(rs.getString("sCorrectedFileName"));
                    studentTest.setCorrectedDownPath(rs.getString("sCorrectedDownPath"));
                    studentTest.setCorrectShowPath(rs.getString("sCorrectedShowPath"));

                    return studentTest;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("学生作业文件信息为null");
            return null;
        }
    }

    /**
     * 保存学生作业文件信息
     * @param studentTest 学生作业实体类
     * @return int
     * 
     * @author cx
     * @since 2015/4/18
     */
    public int saveSTFileInfo(StudentTest studentTest) {
        String sql = "INSERT INTO t_student_test(iStudentId,iTestId,sSubmittedFileName,sSubmittedDownPath,sSubmittedShowPath," +
                "sCorrectedFileName,sCorrectedDownPath,sCorrectedShowPath) VALUES(:studentId,:testId,:submittedFileName," +
                ":submittedDownPath,:submittedShowPath,:correctedFileName,:correctedDownPath,:correctedShowPath)";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("studentId", studentTest.getStudentId())
                .addValue("testId", studentTest.getTestId())
                .addValue("submittedFileName", studentTest.getSubmittedFileName())
                .addValue("submittedDownPath", studentTest.getSubmittedDownPath())
                .addValue("submittedShowPath", studentTest.getSubmittedShowPath())
                .addValue("correctedFileName", studentTest.getCorrectedFileName())
                .addValue("correctedDownPath", studentTest.getCorrectedDownPath())
                .addValue("correctedShowPath", studentTest.getCorrectShowPath());

        KeyHolder keyHolder = new GeneratedKeyHolder();
        int result = namedParameterJdbcTemplate.update(sql, sps, keyHolder);
        studentTest.setId(keyHolder.getKey().longValue());

        log.debug("保存学生作业文件信息");
        return result;
    }

    /**
     * 更新学生作业文件信息
     * @param studentTest 学生作业实体类
     * @return int
     * 
     * @author cx
     * @since 2015/4/18
     */
    public int updateSTFileInfo(final StudentTest studentTest) {
        String sql = "UPDATE t_student_test SET sSubmittedFileName=:submittedFileName,sSubmittedDownPath=:submittedDownPath," +
                "sSubmittedShowPath=:submittedShowPath,sCorrectedFileName=:correctedFileName,sCorrectedDownPath=:correctedDownPath," +
                "sCorrectedShowPath=:correctedShowPath WHERE id=:id";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("submittedFileName", studentTest.getSubmittedFileName())
                .addValue("submittedDownPath", studentTest.getSubmittedDownPath())
                .addValue("submittedShowPath", studentTest.getSubmittedShowPath())
                .addValue("correctedFileName", studentTest.getCorrectedFileName())
                .addValue("correctedDownPath", studentTest.getCorrectedDownPath())
                .addValue("correctedShowPath", studentTest.getCorrectShowPath())
                .addValue("id", studentTest.getId());

        log.debug("更新学生作业文件信息");
        return namedParameterJdbcTemplate.update(sql, sps);
    }
}


